---
id: payments-db
name: Payments DB
version: 0.0.1
container_type: database
technology: postgres@15
authoritative: true
access_mode: readWrite
classification: regulated
retention: 10y
residency: eu-west-1
summary: Primary database for payment transactions and payment method records
---

<NodeGraph />

### What is this?
Payments DB is the authoritative database for all payment transactions, payment methods, and related financial data. It uses PostgreSQL 15 and serves as the system of record for payment processing, refunds, and transaction history.

### What does it store?
- **Payments**: Transaction records including amount, currency, status, timestamps, and gateway references
- **Payment Methods**: Tokenized payment method details (cards, bank accounts) with PCI-compliant storage
- **Refunds**: Refund records linked to original payment transactions
- **Payment Attempts**: Historical record of all payment attempts for debugging and analytics
- **Relationships**: Payment methods belong to customers, payments reference orders and payment methods

### Who writes to it?
- **PaymentService** creates payment records, processes transactions, and manages payment methods
- **PaymentGatewayService** updates payment status based on gateway responses
- Transaction records are immutable after settlement for audit compliance

### Who reads from it?
- **FraudDetectionService** analyzes payment patterns and transaction history
- **BillingService** retrieves payment history for invoice generation
- **OrdersService** checks payment status for order fulfillment
- **SubscriptionService** validates payment methods for recurring charges
- **Finance/Analytics** teams for reconciliation and reporting

### High-level data model
- A `payment` belongs to an `order` and uses a `payment_method`
- Payment lifecycle tracked via `status`: `INITIATED`, `AUTHORIZED`, `CAPTURED`, `FAILED`, `REFUNDED`, `CANCELLED`
- Monetary values stored as integer cents to avoid floating-point issues
- All transactions are idempotent using `idempotency_key`
- Tables: `payments`, `payment_methods`, `refunds`

### Common queries
```sql
-- Get payment details with method info
SELECT p.*, pm.type AS payment_method_type, pm.last_four, pm.brand
FROM payments p
LEFT JOIN payment_methods pm USING (payment_method_id)
WHERE p.payment_id = $1;

-- List customer payments (for payment history)
SELECT *
FROM payments
WHERE customer_id = $1
ORDER BY created_at DESC
LIMIT $2 OFFSET $3;

-- Find failed payments for retry analysis
SELECT payment_id, order_id, failure_reason, created_at
FROM payments
WHERE status = 'FAILED'
  AND created_at >= now() - interval '24 hours'
ORDER BY created_at DESC;

-- Calculate daily payment volume
SELECT date_trunc('day', captured_at) AS day,
       COUNT(*) AS transaction_count,
       SUM(amount_cents)/100.0 AS total_amount
FROM payments
WHERE status = 'CAPTURED'
  AND captured_at >= now() - interval '30 days'
GROUP BY 1
ORDER BY 1;
```

### Lifecycle and data flow
1. Customer initiates payment → `PaymentService` creates record with status `INITIATED`
2. Payment gateway authorization → status updated to `AUTHORIZED`
3. Capture after order fulfillment → status updated to `CAPTURED`
4. Refunds create separate `refunds` record linked to original payment
5. All state changes emit domain events for downstream consumers

### Security and compliance
- **PCI DSS Level 1 compliance**: Card data tokenized via payment gateway, no raw card numbers stored
- **Encryption at rest**: Database encrypted using AWS KMS
- **Encryption in transit**: TLS 1.3 for all connections
- **Access control**: Role-based access, audit logging enabled
- **PII handling**: Customer PII encrypted, access logged for GDPR compliance

### Access patterns and guidance
- Use `payment_id` for point lookups; indexed by `order_id` and `customer_id`
- Query by `idempotency_key` to prevent duplicate charges
- Payments are immutable after `CAPTURED` status - use refunds for reversals
- Never store raw card numbers - always use gateway tokens
- Use read replicas for analytics queries to avoid impacting transaction processing

### Retention and residency
- **Retention**: 10 years (regulatory requirement for financial records)
- **Residency**: `eu-west-1` (GDPR compliance, data localization)
- Archived records older than 3 years moved to cold storage

### Backups and recovery
- Automated continuous backups with 35-day retention
- Point-in-time recovery with 5-minute RPO
- Cross-region backup replication for disaster recovery
- Monthly restore testing to validate RTO objectives

### Monitoring and alerts
- Transaction latency monitored (p99 < 200ms)
- Failed payment rate alerts (threshold: > 5%)
- Database connection pool monitoring
- Slow query alerts for queries > 1 second

### Requesting access
To request access to the Payments DB:

1. **Development/Staging access**:
   - Submit an access request via [ServiceNow](https://company.service-now.com)
   - Select "Database Access Request" → "Payments DB (Non-Production)"
   - Specify read-only or read-write access level
   - Approval required from Payment Service team lead
   - Access granted within 24 hours

2. **Production access**:
   - Production read access requires manager approval + security review
   - Production write access is restricted to PaymentService automated deployments only
   - For emergency production access, contact on-call via PagerDuty
   - All production queries are logged and audited for compliance

3. **Analytics/Reporting access**:
   - Use the read-replica endpoint for reporting queries
   - Request access via #payments-data Slack channel
   - Data export requests require data governance approval due to PCI/GDPR

**Contact**: For questions, contact the Payment Service team:
- Slack: #payments-team
- Email: payments-team@company.com
- Team lead: @dboyne

### Local development
- Connection string: `PAYMENTS_DB_URL` environment variable
- Seed data available via `npm run seed:payments`
- Test payment methods use gateway test tokens
- Local development uses Docker: `docker-compose up payments-db`

### Common issues and troubleshooting
- **Duplicate payments**: Always include `idempotency_key` in payment creation
- **Stuck authorizations**: Run daily job to release authorizations older than 7 days
- **Gateway timeouts**: Implement retry logic with exponential backoff
- **Currency mismatches**: Validate order currency matches payment currency

For more information, see the PaymentService documentation and PCI compliance guidelines.